﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public sealed class D_FIM_ChequeBooks : IDAL_FIM_ChequeBooks
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();
        //

        public D_FIM_ChequeBooks()
        {

        }

        public IList<M_FIM_ChequeBooks> GetFull(Nullable<int> bank_acc_seq, string cheque_book_status)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_ChequeBooks> infoList = new List<M_FIM_ChequeBooks>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bank_acc_seq", bank_acc_seq);
                dbManager.AddParameters(2, "@cheque_book_status", cheque_book_status); 

                strSQL = "SELECT cb.cheque_book_seq, cb.cheque_book_no, cb.bank_acc_seq"
                    + ", cb.start_cheque_no, cb.end_cheque_no, cb.remarks, cb.cheque_book_status"
                    
                    + ", acc.bank_acc_no, acc.bank_acc_name"
                    + ", case when cb.cheque_book_status = '1' then 'พร้อมใช้'"
                        + " else case when cb.cheque_book_status = '2' then 'หมดแล้ว'"
                            + " else 'ยกเลิก'"
                            + " end"
                        + " end as cheque_book_status_name"
                    + ", cb.record_status, rs.record_status_name"
                    + ", cb.created_by, cb.created_when, cb.updated_by, cb.updated_when"
                    + " FROM fim_cheque_books cb"
                    + " LEFT JOIN fim_mas_bank_accounts acc ON cb.bank_acc_seq = acc.bank_acc_seq"
                    //+ " LEFT JOIN fim_mas_bank_branchs bb ON acc.branch_seq = bb.branch_seq"
                    //+ " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                    + " LEFT JOIN sys_record_status rs ON cb.record_status = rs.record_status"
                    + " WHERE cb.record_status not like @delete_status"
                    + (bank_acc_seq != null ? " and cb.bank_acc_seq = @bank_acc_seq" : "")
                    + (cheque_book_status != null ? " and cb.cheque_book_status = @cheque_book_status" : "");
                //

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_ChequeBooks info = new M_FIM_ChequeBooks();
                        info.cheque_book_seq = Convert.ToInt32(drc[i]["cheque_book_seq"]);
                        info.cheque_book_no = drc[i]["cheque_book_no"].ToString();
                        info.bank_acc_seq = cn.ConvertNullInt(drc[i]["bank_acc_seq"]);
                        info.start_cheque_no = cn.ConvertNullInt(drc[i]["start_cheque_no"]);
                        info.end_cheque_no = cn.ConvertNullInt(drc[i]["end_cheque_no"]);
                        info.cheque_book_status = drc[i]["cheque_book_status"].ToString();
                        info.bank_acc_no = drc[i]["bank_acc_no"].ToString();
                        info.bank_acc_name = drc[i]["bank_acc_name"].ToString();
                        info.remarks = drc[i]["remarks"].ToString();
                        info.cheque_book_status_name = drc[i]["cheque_book_status_name"].ToString();

                        info.record_status = drc[i]["record_status"].ToString();
                        info.record_status_name = drc[i]["record_status_name"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_ChequeBooks> GetList(Nullable<int> bank_acc_seq, string cheque_book_status)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_ChequeBooks> infoList = new List<M_FIM_ChequeBooks>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bank_acc_seq", bank_acc_seq);
                dbManager.AddParameters(2, "@cheque_book_status", cheque_book_status);

                strSQL = "SELECT cb.cheque_book_seq, cb.cheque_book_no"
                    + " FROM fim_cheque_books cb"
                    + " LEFT JOIN fim_mas_bank_accounts acc ON cb.bank_acc_seq = acc.bank_acc_seq"
                    + " WHERE cb.record_status not like @delete_status"
                    + (bank_acc_seq != null ? " and cb.bank_acc_seq = @bank_acc_seq" : "")
                    + (cheque_book_status != null ? " and cb.cheque_book_status = @cheque_book_status" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_ChequeBooks info = new M_FIM_ChequeBooks();
                        info.cheque_book_seq = Convert.ToInt32(drc[i]["cheque_book_seq"]);
                        info.cheque_book_no = drc[i]["cheque_book_no"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_FIM_ChequeBooks GetInfo(string cheque_book_no)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_ChequeBooks info = new M_FIM_ChequeBooks();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@cheque_book_no", cheque_book_no);

                strSQL = "SELECT cb.cheque_book_seq, cb.cheque_book_no, cb.bank_acc_seq"
                    + ", cb.start_cheque_no, cb.end_cheque_no, cb.remarks, cb.cheque_book_status"

                    + ", acc.bank_acc_no, acc.bank_acc_name"
                    + ", case when cb.cheque_book_status = '1' then 'พร้อมใช้'"
                        + " else case when cb.cheque_book_status = '2' then 'หมดแล้ว'"
                            + " else 'ยกเลิก'"
                            + " end"
                        + " end as cheque_book_status_name"
                    + ", cb.record_status, rs.record_status_name"
                    + ", cb.created_by, cb.created_when, cb.updated_by, cb.updated_when"
                    + " FROM fim_cheque_books cb"
                    + " LEFT JOIN fim_mas_bank_accounts acc ON cb.bank_acc_seq = acc.bank_acc_seq"
                    //+ " LEFT JOIN fim_mas_bank_branchs bb ON acc.branch_seq = bb.branch_seq"
                    //+ " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                    + " LEFT JOIN sys_record_status rs ON cb.record_status = rs.record_status"
                    + " WHERE cb.cheque_book_no = @cheque_book_no"
                    + " and cb.record_status not like 'D'";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.cheque_book_seq = Convert.ToInt32(drc[0]["cheque_book_seq"]);
                    info.cheque_book_no = drc[0]["cheque_book_no"].ToString();
                    info.bank_acc_seq = cn.ConvertNullInt(drc[0]["bank_acc_seq"]);
                    info.start_cheque_no = cn.ConvertNullInt(drc[0]["start_cheque_no"]);
                    info.end_cheque_no = cn.ConvertNullInt(drc[0]["end_cheque_no"]);
                    info.cheque_book_status = drc[0]["cheque_book_status"].ToString();
                    info.bank_acc_no = drc[0]["bank_acc_no"].ToString();
                    info.bank_acc_name = drc[0]["bank_acc_name"].ToString();
                    info.remarks = drc[0]["remarks"].ToString();
                    info.cheque_book_status_name = drc[0]["cheque_book_status_name"].ToString();

                    info.record_status = drc[0]["record_status"].ToString();
                    info.record_status_name = drc[0]["record_status_name"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["created_by"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["created_when"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["updated_by"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["updated_when"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public M_FIM_ChequeBooks GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_ChequeBooks info = new M_FIM_ChequeBooks();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT cb.cheque_book_seq, cb.cheque_book_no, cb.bank_acc_seq"
                    + ", cb.start_cheque_no, cb.end_cheque_no, cb.remarks, cb.cheque_book_status"

                    + ", acc.bank_acc_no, acc.bank_acc_name"
                    + ", case when cb.cheque_book_status = '1' then 'พร้อมใช้'"
                        + " else case when cb.cheque_book_status = '2' then 'หมดแล้ว'"
                            + " else 'ยกเลิก'"
                            + " end"
                        + " end as cheque_book_status_name"
                    + ", cb.record_status, rs.record_status_name"
                    + ", cb.created_by, cb.created_when, cb.updated_by, cb.updated_when"
                    + " FROM fim_cheque_books cb"
                    + " LEFT JOIN fim_mas_bank_accounts acc ON cb.bank_acc_seq = acc.bank_acc_seq"
                    //+ " LEFT JOIN fim_mas_bank_branchs bb ON acc.branch_seq = bb.branch_seq"
                    //+ " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                    + " LEFT JOIN sys_record_status rs ON cb.record_status = rs.record_status"
                    + " WHERE cb.cheque_book_seq = @seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.cheque_book_seq = Convert.ToInt32(drc[0]["cheque_book_seq"]);
                    info.cheque_book_no = drc[0]["cheque_book_no"].ToString();
                    info.bank_acc_seq = cn.ConvertNullInt(drc[0]["bank_acc_seq"]);
                    info.start_cheque_no = cn.ConvertNullInt(drc[0]["start_cheque_no"]);
                    info.end_cheque_no = cn.ConvertNullInt(drc[0]["end_cheque_no"]);
                    info.cheque_book_status = drc[0]["cheque_book_status"].ToString();
                    info.bank_acc_no = drc[0]["bank_acc_no"].ToString();
                    info.bank_acc_name = drc[0]["bank_acc_name"].ToString();
                    info.remarks = drc[0]["remarks"].ToString();
                    info.cheque_book_status_name = drc[0]["cheque_book_status_name"].ToString();

                    info.record_status = drc[0]["record_status"].ToString();
                    info.record_status_name = drc[0]["record_status_name"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["created_by"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["created_when"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["updated_by"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["updated_when"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        //public string SelectRecentInsertedRoleSeq()
        //{
        //    string strSQL;
        //    string strResult;

        //    try
        //    {
        //        dbManager.Open();

        //        strSQL = "select scope_identity() as last_seq";

        //        strResult = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0]["last_seq"].ToString();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return strResult;
        //}

        public string Insert(M_FIM_ChequeBooks info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(10);

                dbManager.AddParameters(0, "@cheque_book_seq", info.cheque_book_seq);
                dbManager.AddParameters(1, "@cheque_book_no", info.cheque_book_no);
                dbManager.AddParameters(2, "@bank_acc_seq", info.bank_acc_seq);
                dbManager.AddParameters(3, "@start_cheque_no", info.start_cheque_no);
                dbManager.AddParameters(4, "@end_cheque_no", info.end_cheque_no);
                dbManager.AddParameters(5, "@remarks", info.remarks);
                dbManager.AddParameters(6, "@cheque_book_status", "1");

                dbManager.AddParameters(7, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(8, "@created_by", info.created_by);
                dbManager.AddParameters(9, "@created_when", info.created_when);

                strSQL = "INSERT INTO fim_cheque_books"
                    + "(cheque_book_no, bank_acc_seq, start_cheque_no, end_cheque_no, remarks, cheque_book_status"
                    + ", record_status, created_by, created_when)"
                    + " VALUES (@cheque_book_no, @bank_acc_seq, @start_cheque_no, @end_cheque_no, @remarks, @cheque_book_status"
                        + ", @record_status, @created_by, @created_when);"
                    + " select scope_identity();";

                var result = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();

                return result;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_FIM_ChequeBooks info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(9);

                dbManager.AddParameters(0, "@cheque_book_seq", info.cheque_book_seq);
                dbManager.AddParameters(1, "@cheque_book_no", info.cheque_book_no);
                dbManager.AddParameters(2, "@bank_acc_seq", info.bank_acc_seq);
                dbManager.AddParameters(3, "@start_cheque_no", info.start_cheque_no);
                dbManager.AddParameters(4, "@end_cheque_no", info.end_cheque_no);
                dbManager.AddParameters(5, "@remarks", info.remarks);
                dbManager.AddParameters(6, "@cheque_book_status", info.cheque_book_status);

                dbManager.AddParameters(7, "@updated_by", info.updated_by);
                dbManager.AddParameters(8, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_cheque_books"
                    + " set cheque_book_no = @cheque_book_no"
                    + ", bank_acc_seq = @bank_acc_seq"
                    //+ ", start_cheque_no = @start_cheque_no"
                    //+ ", end_cheque_no = @end_cheque_no"
                    + ", remarks = @remarks"
                    + ", cheque_book_status = @cheque_book_status"

                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE cheque_book_seq = @cheque_book_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(M_FIM_ChequeBooks info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@cheque_book_seq", info.cheque_book_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_cheque_books"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE cheque_book_seq = @cheque_book_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                strSQL = "UPDATE fim_cheques"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE cheque_book_seq = @cheque_book_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
